﻿using System;
using System.Collections.Generic;
using System.Data;
using Aptus.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Web;
using System.Configuration;

namespace App_Code
{
    public class ChangeDataCaptureAuditController
	{

        public string ConnectionString
        {
            get
            {
                return
                    ConfigurationManager.ConnectionStrings["ConnectionString"].
                        ConnectionString;
            }
        }

        //gets a list of transactions by table name and entityID/primary key
        public List<TransationDataListResult> GetTransList(string tableName, int entityId)
        {
            try
            {

             if (string.IsNullOrEmpty(tableName) || entityId < 1) return null;
 
            var items = new List<TransationDataListResult>();
            var sql = "DECLARE @from_lsn binary(10), @to_lsn binary(10) ";
            sql = sql + "SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_" +tableName+ "') ";
            sql = sql + "SET @to_lsn   = sys.fn_cdc_get_max_lsn() ";
            sql = sql + "SELECT cast(d.__$start_lsn as int) as TransID , d.ID, d.ChangedBy,dbo.GetAuditOperation( d.__$operation) ";
            sql = sql + "as ChangeType, t.tran_begin_time as ChangedDate FROM cdc.fn_cdc_get_all_changes_dbo_" + tableName;
            sql = sql + " (@from_lsn, @to_lsn, N'all') d inner join cdc.lsn_time_mapping t on d.__$start_lsn=t.start_lsn ";
            sql = sql + "where d.Id=" + entityId.ToString();
            sql = sql + " order by ChangedDate desc;";

            //var sql = "select n.ID, cast(n.ChangeData as varchar(50)) as ChangeData,n.sysCreatedDate, dbo.GetContactNameByUserID(n.UserID) as UserName from " + tableName + " n where n.EntityID=" + entityId.ToString() + " AND (n.sysIsDeleted is null or n.sysIsDeleted=0)";

            using (SqlConnection conn = new SqlConnection(ConnectionString))
          {  
              conn.Open();
              var cmd = new SqlCommand(sql, conn);
              using (var reader = cmd.ExecuteReader())
              {
                 while (reader.Read())
                  {
                    var item = new TransationDataListResult();
                    item.ID = (int)reader["ID"];
                     var transId= reader["TransID"];
                    if (transId != DBNull.Value)
                        item.TransID = (int)transId;
                    var changeType = reader["ChangeType"];
                    if (changeType != DBNull.Value)
                        item.ChangeType = (string)changeType;
                    var changedBy = reader["ChangedBy"];
                    if (changedBy != DBNull.Value)
                        item.ChangedBy = (string)changedBy;
                    var date = reader["ChangedDate"];
                    if (date != DBNull.Value)
                        item.ChangedDate = (DateTime)date;
                    items.Add(item);
                    
                  }

              }
          }
            return items;
     
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        //Exclude column types like image and byte types for performance reasons
        private bool IsAcceptableType(DataColumn c)
        {
            if (c == null)
                return false;
           

            if (c.DataType.FullName != null)
            {

                 if(c.ColumnName=="Description" )
                 {
                     var s = "";
                 }

                var typeName = c.DataType.FullName.ToLower();
                if (typeName.Contains("byte") || typeName.Contains("image") || typeName.Contains("binary"))
                {
                    return false;
                }
                else
                {
                    return true;
                }

            }
            else
            {
                return true;
            }
          
        }

        //check if a column has null
        private bool BothNull(object obj1,object obj2)
        {
            if (obj1 == null && obj2 == null)
                return true;
            else if (obj1.ToString() == "" && obj2.ToString() == "")
                return true;
            else
                return false;
        }

      //inverts your normal data table into a table of ColumnName, OldValue, NewValue
        private List<ChangeDataListResult> GetValueChanges(DataTable tableData)
        {
            try
            {
                
          
            var items = new List<ChangeDataListResult>();

            if (tableData!=null )
            {
                if(tableData.Rows.Count>1  )
                {
                    DataRow r1 = tableData.Rows[0];
                    DataRow r2 = tableData.Rows[1];

                    foreach(DataColumn c in tableData.Columns)
                    {
                        if (IsAcceptableType(c))
                        {
                            var oldValue = r1[c.ColumnName];
                            var newValue = r2[c.ColumnName];
                            oldValue = oldValue ?? "";
                            newValue = newValue ?? "";
                            if (!BothNull(oldValue, newValue) && !oldValue.Equals(newValue))
                            {

                                var item = new ChangeDataListResult();
                                item.ColumnName = c.ColumnName;
                                item.OldValue = oldValue.ToString();
                                item.NewValue = newValue.ToString();
                                items.Add(item);
                            }
                        }
                    }
                }
                else if (tableData.Rows.Count==1)
                {
                    DataRow r1 = tableData.Rows[0];
                    foreach (DataColumn c in tableData.Columns)
                    {
                        if (IsAcceptableType(c))
                        {
                            var newValue = r1[c.ColumnName] != null ? r1[c.ColumnName].ToString() : "";
                            if (!string.IsNullOrEmpty(newValue))
                            {
                                var item = new ChangeDataListResult();
                                item.ColumnName = c.ColumnName;
                                item.NewValue = newValue;
                                items.Add(item);
                            }
                        }
                    }
                }
            }

            return items;
                  }
            catch (Exception ex)
            {
                return null;
            }
        }

        //gets column changes by transaction Id
        public List<ChangeDataListResult> GetChanges(string tableName, string transId)
        {
            try
            {
                var items = new List<ChangeDataListResult>();
                var sql = "DECLARE @from_lsn binary(10), @to_lsn binary(10) ";
                sql = sql + "SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_" + tableName + "') ";
                sql = sql + "SET @to_lsn   = sys.fn_cdc_get_max_lsn() ";
                sql = sql + "SELECT cast(d.__$start_lsn as int) as TransID, d.* into #tempTable  FROM cdc.fn_cdc_get_all_changes_dbo_" + tableName;
                sql = sql + " (@from_lsn, @to_lsn, N'all update old') d";
                sql = sql + " where d.__$start_lsn=" + transId;
                sql = sql + "; alter table #tempTable drop column __$Start_lsn;";
                sql = sql + " alter table #tempTable drop column __$seqval;";
                sql = sql + " alter table #tempTable drop column __$operation;";
                sql = sql + " alter table #tempTable drop column __$update_mask;";
                sql = sql + " alter table #tempTable drop column changedby;";
                sql = sql + " select * from #tempTable;";
                var dataTables = GetDataTables(sql);

                if (dataTables != null && dataTables.Count > 0)
                    return GetValueChanges(dataTables[0]);
                else
                    return null; 
            }
            catch (Exception)
            {
                
                return null ;
            }
           

        }

        //returns datatable collection given a sql selectText
        private  DataTableCollection GetDataTables(string selectText)
        {
            try
            {
                System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(ConnectionString);
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                con.Open();
                cmd.Connection = con;
                cmd.CommandText = selectText;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (ds != null)
                {
                    return ds.Tables;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }

        }

        //retrives an int from string parameter.
        public static int GetInt(string ParseString)
        {
            int i = -1;
            if (!string.IsNullOrEmpty(ParseString))
            {
                bool bApplication = int.TryParse(ParseString, out i);
            }
            return i;
        }
	}
}
 